﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetMemberInParentClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetMemberInParentClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetMemberInParentClass] 
    @ClassID int
/*

============================================================
功能:    得到父组的所有成员
参数:
    @ClassID int        :    组（类）ID
============================================================

*/

AS

Declare @ParentID int
--得到父组ID
SELECT @parentID=classparentid 
    FROM 
        uds_class 
    WHERE classid = @ClassID

IF @parentID >0
BEGIN
    SELECT * 
        FROM 
            uds_staff 
        WHERE   staff_id in (
                    SELECT staff_id 
                        FROM 
                            uds_staff_in_team 
                        WHERE team_id = @parentID
                     ) 
            and dimission=0
END
ELSE
    SELECT * 
        FROM 
            uds_staff 
        WHERE 1=2